![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
FragmentationFragmentation occurs when pieces of the database are no longer contiguous. Fragmentation can consist of disk fragmentation or tablespace fragmentation. Both types of fragmentation usually affect performance. Disk fragmentation usually causes multiple I/Os to occur where one I/O should have been sufficient (such as with chained or migrated rows). Disk fragmentation can also be caused when the extents that make up the database segments are noncontiguous, as happens when there is excessive dynamic growth (see Figure 10.3).
Tablespace fragmentation is caused by the dropping and creation of segments, which can cause large free areas between segments. The free areas result in the inefficient use of space and cause excessive disk seeks over the empty areas (see Figure 10.4). Tablespace fragmentation can also prevent Oracle from taking advantage of multiblock reads.
Tablespace fragmentation can be detected by looking in the Oracle table DBA_EXTENTS with a query such as this one: SQL> SELECT SUBSTR(tablespace_name,1,25) "Tablespace Name", 2 block_id "Block", 3 blocks "Number of Blocks", 4 SUBSTR(segment_name,1,25) "Segment Name" 5 FROM dba_extents 6 WHERE tablespace_name = 'SYSTEM' 7 ORDER BY block_id; Tablespace Name Block Number of Blocks Segment Name ------------------------ -------- ---------------- ------------------------ SYSTEM 2 25 SYSTEM SYSTEM 27 25 SYSTEM SYSTEM 52 60 C_OBJ# SYSTEM 112 5 I_OBJ# SYSTEM 117 5 C_TS# SYSTEM 122 5 I_TS# SYSTEM 127 10 C_FILE#_BLOCK# SYSTEM 137 5 I_FILE#_BLOCK# SYSTEM 142 5 C_USER# SYSTEM 147 5 I_USER# SYSTEM 152 5 UNDO$ SYSTEM 157 5 FILE$ SYSTEM 162 5 OBJ$ SYSTEM 167 5 CON$ SYSTEM 172 25 C_COBJ# SYSTEM 197 5 I_COBJ# SYSTEM 202 5 I_TAB1 SYSTEM 207 5 I_UNDO1 SYSTEM 212 5 I_OBJ1 SYSTEM 217 5 I_OBJ2 SYSTEM 222 5 I_IND1 By examining the blocks, you can determine whether there are any segments missinga process that is quite time consuming and tedious. Another way to detect fragmentation is by using one of many third-party monitoring and defragmentation tools. One way to eliminate fragmentation is to export the table or tablespace data, remove and re-create the table or tablespace, and import the data. Although this process fixes the problem at hand, it may not solve the problem that caused the fragmentation to occur in the first place. You can prevent fragmentation by properly sizing segment storage parameters so that your tables dont span excessive numbers of extents. It can also help to group segments with similar growth characteristics in the same tablespaces. It is also beneficial to avoid unnecessary table or index drops, which also cause fragmentation. Probably of most importance is to separate temporary segments into their own tablespaces. By eliminating fragmentation, you can reduce excessive I/O and CPU usage, streamlining data accesses. And when you reduce any overhead and unnecessary I/O, you improve system performance. Hash ClustersA hash cluster is similar to a cluster but uses a hash function rather than an index to reference the cluster key. A hash cluster stores the data based on the result of a hash function. The hash function is a numeric function that determines the data block in the cluster based on the value of the cluster key. Figure 10.5 shows a hash cluster.
To find the data block in an index cluster, there must first be one or more I/Os to the cluster index to find the correct data block. In a hash cluster, the cluster key itself tells Oracle where the data block is, an arrangement that can reduce to one the number of I/Os needed to retrieve the row. In contrast to the index cluster, which stores related data together based on the rows cluster key value, the hash cluster stores related rows together based on their hash values. The number of hash values is determined by the HASHKEYS value parameter of the CREATE CLUSTER command. The number and size of the cluster keys are very important and should be carefully calculated. Do not use hash clusters on tables where table scans are often performed on only one of the tables in the cluster. The additional space required by the cluster and additional I/O can reduce performance. Do not use a hash cluster on a table where the application frequently modifies the cluster key or when the table is constantly being modified. Because the cluster key is based on a calculation, significant overhead is involved in constantly recalculating the key.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |